package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.SelectProductBySupplierDao;
import com.clqteam.model.Product;
import com.clqteam.model.Supplier;
import com.clqteam.util.JDBCutil;
/**
 * 通过供应商查询其提供的货物
 * @author Administrator LXG
 *
 */
public class SelectProductBySupplierDaoImpl implements SelectProductBySupplierDao{

	
	//分页查询product信息
	@Override
	public List<Product> selectProductBySupId(int supId, int page, int rows) {
		List <Product>li=new ArrayList();
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT s_product.pId,s_product.pName,s_product.pType,s_product.pAmount,s_product.pClass,");
		sql.append(" s_product.pCost,s_product.pPrice,s_product.pTime,s_product.supId");
		sql.append(" FROM s_product");
		sql.append(" INNER JOIN s_supplier");
		sql.append(" ON s_product.supId=s_supplier.supId");
		sql.append(" WHERE s_supplier.supId=?");
		sql.append(" LIMIT ?,?");
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());						
			ps.setInt(1, supId);
			ps.setInt(2, (page-1)*rows);
			ps.setInt(3, rows);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				Product pro=new Product();
				pro.setpId(rs.getInt("pId"));
				pro.setpName(rs.getString("pName"));
				pro.setpType(rs.getString("pType"));
				pro.setpAmount(rs.getInt("pAmount"));
				pro.setpClass(rs.getString("pClass"));
				pro.setpCost(rs.getDouble("pCost"));
				pro.setpPrice(rs.getDouble("pPrice"));
				pro.setpTime(rs.getString("pTime"));
				pro.setSupId(rs.getInt("supId"));
				li.add(pro);
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return li;
	}
	//查询product信息的总数
	@Override
	public int countProductBySupId(int supId) {
		int count=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT COUNT(s_product.pId) AS tot");		
		sql.append(" FROM s_product");
		sql.append(" INNER JOIN s_supplier");
		sql.append(" ON s_product.supId=s_supplier.supId");
		sql.append(" WHERE s_supplier.supId=?");		
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());						
			ps.setInt(1, supId);
			
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt("tot");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
